/**
 * DaoGas.java
 *2013年10月6日下午1:11:51
 *LINKT
 */
package com.jskj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.jskj.bean.Gas;
import com.jskj.commons.DBConn;

/**
 * @author LINKT
 *
 */
public class DaoGas {

        public String getGasList(int page ,int limit,String sql) {
                DBConn conn=new DBConn();
                String str="";
                int rowEnd=page*limit;
                String sSQL="select * from ("+
                                "    select top "+limit+" * from("+
                                "   select top "+rowEnd+" * "+
                                "    from ("+sql+") as aa order by jyz_bh) as bb order by jyz_bh desc ) as cc order by jyz_bh ";
                int totalcount=conn.SelectSQLCount(sql);
                StringBuffer sb=new StringBuffer();
                ResultSet rs=conn.SelectSQL(sSQL);
                try {
                        while (rs.next()) {
                                sb.append("{\"jyz_swjg\":\""+rs.getString("jyz_swjg")+"\",\"jyz_swjg_mc\":\""+rs.getString("jyz_swjg_mc")+"\",\"jyz_bh\":\""+rs.getString("jyz_bh")+"\","
                                                + "\"jyz_mc\":\""+rs.getString("jyz_mc")+"\","
                                                + "\"jyz_dz\":\""+rs.getString("jyz_dz")+"\","
                                                + "\"jyz_lxr\":\""+rs.getString("jyz_lxr")+"\","
                                                + "\"jyz_xzqh\":\""+rs.getString("jyz_xzqh")+"\","
                                                + "\"jyz_xzqh_mc\":\""+rs.getString("jyz_xzqh_mc")+"\","
                                                + "\"jyz_sh\":\""+rs.getString("jyz_sh")+"\","
                                                + "\"jyz_dh\":\""+rs.getString("jyz_dh")+"\","
                                                + "\"jyz_bz\":\""+rs.getString("jyz_bz")+"\""
                                                                + "},");
                        }
                        rs.close();
                        conn.Close();
                        if (sb.length()>0) {
                                sb=sb.deleteCharAt(sb.length()-1);
                        }
                        str="{\"totalCount\":"+totalcount+",\"items\":["+sb.toString()+"]}";;
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return str;
        }
        public String getGasListForLeaf(String jyz_xzqh) {
                DBConn conn=new DBConn();
                String str="";
                String sSQL="select * from jyz where jyz_xzqh='"+jyz_xzqh+"' and jyz_bz='N' and jyz_bh not in(select jyz_ptz from jyzgx)";
                StringBuffer sb=new StringBuffer();
                ResultSet rs=conn.SelectSQL(sSQL);
                try {
                        while (rs.next()) {
                                sb.append("{\"id\":\""+rs.getString("jyz_bh")+"\",\"text\":\""+rs.getString("jyz_mc")+"\",\"leaf\":\"1\",\"checked\":false},");
                        }
                        rs.close();
                        conn.Close();
                        if (sb.length()>0) {
                                sb=sb.deleteCharAt(sb.length()-1);
                        }
                        str="["+sb.toString()+"]";;
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return str;
        }
        public String getZXGasListForLeaf(String jyz_xzqh) {
                DBConn conn=new DBConn();
                String str="";
                String sSQL="select * from jyz where jyz_xzqh='"+jyz_xzqh+"' and jyz_bz='Y' ";
                StringBuffer sb=new StringBuffer();
                ResultSet rs=conn.SelectSQL(sSQL);
                try {
                        while (rs.next()) {
                                sb.append("{\"id\":\""+rs.getString("jyz_bh")+"\",\"text\":\""+rs.getString("jyz_mc")+"\",\"leaf\":\"1\"},");
                        }
                        rs.close();
                        conn.Close();
                        if (sb.length()>0) {
                                sb=sb.deleteCharAt(sb.length()-1);
                        }
                        str="["+sb.toString()+"]";;
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return str;
        }
        public boolean addGas(Gas gas) {
                DBConn conn=new DBConn();
                if (conn.SelectSQLCount("select * from jyz where jyz_bh='"+gas.getJyz_bh()+"'")==0) {
                        conn.ExecuteSQL("INSERT INTO [taxoil].[dbo].[jyz]([jyz_bh], [jyz_mc], [jyz_sh], [jyz_swjg], [jyz_xzqh], [jyz_dz], [jyz_lxr], [jyz_dh], [jyz_bz]) "+
                                                        "VALUES('"+gas.getJyz_bh()+"', '"+gas.getJyz_mc()+"', '"+gas.getJyz_sh()+"', '"+gas.getJyz_swjg()+"', '"+gas.getJyz_xzqh()+"', '"+gas.getJyz_dz()+"','"+gas.getJyz_lxr()+"', '"+gas.getJyz_dh()+"', '"+gas.getJyz_bz()+"')");
                        conn.Close();
                        return true;
                }else{
                        return false;
                }
        }
        public boolean updateGas(Gas gas) {
                DBConn conn=new DBConn();
                if (conn.SelectSQLCount("select * from jyz where jyz_bh='"+gas.getJyz_bh()+"'")>0) {
                        conn.ExecuteSQL("UPDATE [taxoil].[dbo].[jyz] "+
                                                        "SET  [jyz_mc]='"+gas.getJyz_mc()+"', [jyz_sh]='"+gas.getJyz_sh()+"', [jyz_swjg]='"+gas.getJyz_swjg()+"', [jyz_xzqh]='"+gas.getJyz_xzqh()+"', [jyz_dz]='"+gas.getJyz_dz()+"', [jyz_lxr]='"+gas.getJyz_lxr()+"', [jyz_dh]='"+gas.getJyz_dh()+"', [jyz_bz]='"+gas.getJyz_bz()+"' "+
                                                                "WHERE [jyz_bh]='"+gas.getJyz_bh()+"'");
                        conn.Close();
                        return true;
                }else{
                        return false;
                }
        }
        public boolean delGas(Gas gas) {
                DBConn conn=new DBConn();
                if (conn.SelectSQLCount("select * from jyz where jyz_bh='"+gas.getJyz_bh()+"'")>0) {
                        conn.ExecuteSQL("delete FROM jyz  where jyz_bh='"+gas.getJyz_bh()+"'");
                        conn.ExecuteSQL("delete FROM jyzgx where jyz_zxz='"+gas.getJyz_bh()+"'");
                        conn.ExecuteSQL("delete FROM jyzgx where jyz_ptz='"+gas.getJyz_bh()+"'");
                        conn.Close();
                        return true;
                }else {
                        return false;
                }
               
        }
        public boolean addPTGas(String jyz_zxz,String jyz_ptz) {
                DBConn conn=new DBConn();
                if (conn.SelectSQLCount("select * from jyzgx where jyz_zxz='"+jyz_zxz+"' and jyz_ptz='"+jyz_ptz+"'")==0 && (conn.SelectSQLCount("select * from jyz where jyz_bh='"+jyz_ptz+"'")>0)) {
                        conn.ExecuteSQL("INSERT INTO [taxoil].[dbo].[jyzgx]([jyz_zxz], [jyz_ptz]) "+
                                                        "VALUES('"+jyz_zxz+"', '"+jyz_ptz+"')");
                        conn.Close();
                        return true;
                }else {
                        return false;
                }
        }
        public boolean delPTGas(String jyz_zxz,String jyz_ptz) {
                DBConn conn=new DBConn();
                if (conn.SelectSQLCount("select * from jyzgx where jyz_zxz='"+jyz_zxz+"' and jyz_ptz='"+jyz_ptz+"'")>0) {
                        conn.ExecuteSQL("delete FROM jyzgx where jyz_zxz='"+jyz_zxz+"' and jyz_ptz='"+jyz_ptz+"'");
                        conn.Close();
                        return true;
                }else {
                        return false;
                }
        }
}
